/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Business;

import Objects.Employee;
import Objects.Permission;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Nguyen Kim Manh
 */
public class EmployeeBusiness {

    /**
     * Method isEmployee used to check Employee's Account when each Employee
     * login
     *
     * @return a variable has boolean type
     * @param objEmployee
     */
    public static boolean isEmployee(Employee em) throws ClassNotFoundException, SQLException {
        boolean kt = false;

        String sql = "{call Check_LoginEmployee(?,?)}";
        ArrayList<String> list = new ArrayList<>();
        list.add(em.getEmployeeAccount());
        list.add(em.getEmployeePassword());

        ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list);

        kt = rs.next();

        return kt;
    }

    /**
     * Method getAllEmployee() used to get all employee from database
     *
     * @return ArrayList<Employee>
     */
    public static ArrayList<Employee> getAllEmployee() {
        ArrayList<Employee> list = new ArrayList<>();

        String sql = "{call sp_getAllEmployee}";
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, null);
            while (rs.next()) {
                Employee em = new Employee();
                em.setEmployeeID(rs.getString("EmployeeID"));
                em.setEmployeeName(rs.getString("EmployeeName"));
                em.setEmployeeBirthDay(rs.getDate("EmployeeBirthDay"));
                em.setEmployeeAddress(rs.getString("EmployeeAddress"));
                em.setEmployeePhone(rs.getString("EmployeePhone"));
                em.setEmployeeGender(rs.getString("EmployeeGender"));
                em.setEmployeeAccount(rs.getString("EmployeeAccount"));
                em.setTimeworkID(rs.getString("TimeworkID"));
                em.setPermissionID(rs.getString("PermissionID"));

                list.add(em);
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }

    /**
     * Method getEmployeeByPermission() used to get all employee from database
     *
     * @param permissionID
     * @return ArrayList<Employee>
     */
    public static ArrayList<Employee> getEmployeeByPermission(String per) {
        ArrayList<Employee> list = new ArrayList<>();

        String sql = "{call sp_getEmployeeByPermission(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(per);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Employee em = new Employee();
                em.setEmployeeID(rs.getString("EmployeeID"));
                em.setEmployeeName(rs.getString("EmployeeName"));
                em.setEmployeeBirthDay(rs.getDate("EmployeeBirthDay"));
                em.setEmployeeAddress(rs.getString("EmployeeAddress"));
                em.setEmployeePhone(rs.getString("EmployeePhone"));
                em.setEmployeeGender(rs.getString("EmployeeGender"));
                em.setEmployeeAccount(rs.getString("EmployeeAccount"));
                em.setTimeworkID(rs.getString("TimeworkID"));
                em.setPermissionID(rs.getString("PermissionID"));

                list.add(em);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }

    /**
     * Method getEmployeeByTimework() used to get all employee from database
     *
     * @param permissionID
     * @return ArrayList<Employee>
     */
    public static ArrayList<Employee> getEmployeeByTimework(String tw) {
        ArrayList<Employee> list = new ArrayList<>();

        String sql = "{call sp_getEmployeeByTimework(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(tw);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Employee em = new Employee();
                em.setEmployeeID(rs.getString("EmployeeID"));
                em.setEmployeeName(rs.getString("EmployeeName"));
                em.setEmployeeBirthDay(rs.getDate("EmployeeBirthDay"));
                em.setEmployeeAddress(rs.getString("EmployeeAddress"));
                em.setEmployeePhone(rs.getString("EmployeePhone"));
                em.setEmployeeGender(rs.getString("EmployeeGender"));
                em.setEmployeeAccount(rs.getString("EmployeeAccount"));
                em.setTimeworkID(rs.getString("TimeworkID"));
                em.setPermissionID(rs.getString("PermissionID"));

                list.add(em);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }

    /**
     * method used to add new Employee
     */
    public static int addEmployee(Employee em) throws SQLException {
        int kt = 0;

        Connection cnn = null;
        try {
            cnn = DBAccess.DB_Connect.getConnection();
            String sql = "{call sp_insertEmployee(?,?,?,?,?,?,?,?,?,?)}";
            CallableStatement csm=cnn.prepareCall(sql);

            csm.setString(1,em.getEmployeeID());
             csm.setString(2,em.getEmployeeName());
             csm.setDate(3,em.getEmployeeBirthDay());
             csm.setString(4,em.getEmployeeGender());
             csm.setString(5,em.getEmployeeAddress());
             csm.setString(6,em.getEmployeePhone());
             csm.setString(7,em.getPermissionID());
             csm.setString(8,em.getTimeworkID());
             csm.setString(9,em.getEmployeeAccount());
             csm.setString(10,em.getEmployeePassword());
            
            
            kt=csm.executeUpdate();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            if(cnn!=null){
                cnn.close();
            }
        }


        return kt;
    }

    /**
     * method used to get Employee by Account
     */
    public static Employee getEmployeeByAccount(String account) {
        Employee em = new Employee();

        String sql = "{call sp_getEmployeeByAccount(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(account);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            if (rs.next()) {
                em.setEmployeeID(rs.getString("EmployeeID"));
                em.setEmployeeName(rs.getString("EmployeeName"));
                em.setEmployeeBirthDay(rs.getDate("EmployeeBirthDay"));
                em.setEmployeeAddress(rs.getString("EmployeeAddress"));
                em.setEmployeePhone(rs.getString("EmployeePhone"));
                em.setEmployeeGender(rs.getString("EmployeeGender"));
                em.setEmployeeAccount(rs.getString("EmployeeAccount"));
                em.setTimeworkID(rs.getString("TimeworkID"));
                em.setPermissionID(rs.getString("PermissionID"));
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }


        return em;

    }

    /**
     * method used to get Employee by Name
     */
    public static ArrayList<Employee> getEmployeeByName(String name) {

        ArrayList<Employee> list = new ArrayList<>();
        String sql = "{call sp_getEmployeeByName(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(name);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Employee em = new Employee();
                em.setEmployeeID(rs.getString("EmployeeID"));
                em.setEmployeeName(rs.getString("EmployeeName"));
                em.setEmployeeBirthDay(rs.getDate("EmployeeBirthDay"));
                em.setEmployeeAddress(rs.getString("EmployeeAddress"));
                em.setEmployeePhone(rs.getString("EmployeePhone"));
                em.setEmployeeGender(rs.getString("EmployeeGender"));
                em.setEmployeeAccount(rs.getString("EmployeeAccount"));
                em.setTimeworkID(rs.getString("TimeworkID"));
                em.setPermissionID(rs.getString("PermissionID"));

                list.add(em);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;

    }

    /**
     * method used to get Employee by Name
     */
    public static ArrayList<Employee> getEmployeeByID(String ID) {
        ArrayList<Employee> list = new ArrayList<>();

        String sql = "{call sp_getEmployeeByID(?)}";
        ArrayList list1 = new ArrayList();
        list1.add(ID);
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, list1);
            while (rs.next()) {
                Employee em = new Employee();
                em.setEmployeeID(rs.getString("EmployeeID"));
                em.setEmployeeName(rs.getString("EmployeeName"));
                em.setEmployeeBirthDay(rs.getDate("EmployeeBirthDay"));
                em.setEmployeeAddress(rs.getString("EmployeeAddress"));
                em.setEmployeePhone(rs.getString("EmployeePhone"));
                em.setEmployeeGender(rs.getString("EmployeeGender"));
                em.setEmployeeAccount(rs.getString("EmployeeAccount"));
                em.setTimeworkID(rs.getString("TimeworkID"));
                em.setPermissionID(rs.getString("PermissionID"));

                list.add(em);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;

    }

    /**
     * method used to update Employee Information
     *
     */
    public static int updateEmployee(String emID, String emName, Date emBD, String emGender, String emAdd, String emPhone, String perID, String twID) {
        int result = 0;
        Connection cnn = null;
        try {


            cnn = DBAccess.DB_Connect.getConnection();

            String sql = "{call sp_updateEmployee(?,?,?,?,?,?,?,?)}";
            CallableStatement csm = cnn.prepareCall(sql);
            csm.setString(1, emID);
            csm.setString(2, emName);
            csm.setDate(3, emBD);
            csm.setString(4, emGender);
            csm.setString(5, emAdd);
            csm.setString(6, emPhone);
            csm.setString(7, perID);
            csm.setString(8, twID);

            result = csm.executeUpdate();

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cnn != null) {
                try {
                    cnn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return result;
    }

    /**
     * method used to delete employee
     */
    public static int deleteEmployee(String emID) {
        int rs = 0;
        String sql = "{call sp_deleteEmployee(?)}";
        ArrayList list = new ArrayList();
        list.add(emID);
        try {
            rs = DBAccess.DB_Connect.executeNonQueryPreparedStatement(sql, list);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    /**
     * method get newest employee
     *
     */
    public static Employee getNewestEmployee() {
        Employee em = new Employee();

        String sql = "{call sp_getNewestEmployee}";
        try {
            ResultSet rs = DBAccess.DB_Connect.executeQuery(sql, null);

            if (rs.next()) {
                em.setEmployeeID(rs.getString("EmployeeID"));
                em.setEmployeeName(rs.getString("EmployeeName"));
                em.setEmployeeBirthDay(rs.getDate("EmployeeBirthDay"));
                em.setEmployeeAddress(rs.getString("EmployeeAddress"));
                em.setEmployeePhone(rs.getString("EmployeePhone"));
                em.setEmployeeGender(rs.getString("EmployeeGender"));
                em.setEmployeeAccount(rs.getString("EmployeeAccount"));
                em.setTimeworkID(rs.getString("TimeworkID"));
                em.setPermissionID(rs.getString("PermissionID"));
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }

        return em;
    }
    
    
    /**
     * method used to check the existence of account
     */
    public static boolean checkAccount(String account){
        boolean kt=false;
        
        String sql="{call sp_getEmployeeByAccount(?)}";
        ArrayList list=new ArrayList();
        list.add(account);
        try {
            ResultSet rs=DBAccess.DB_Connect.executeQuery(sql, list);
            if(rs.next()){
                kt=true;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        

        
        return kt;
    }
    
    
    /**
     * this method used to update new password--
     */
    public static int changePassword(String acc,String pass){
        int kt=0;
        
        String sql="{call sp_ChangePassword(?,?)}";
        ArrayList list=new ArrayList();
        list.add(acc);
        list.add(pass);
        try {
            kt=DBAccess.DB_Connect.executeNonQuery(sql, list);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(EmployeeBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return kt;
    }
}
